package org.deschutter;

import org.deschutter.exceptions.*;
import org.deschutter.stats.Ship;


import java.math.BigDecimal;
import java.sql.*;
import java.util.HashMap;
import java.util.ArrayList;

/**
 * User: Berten De Schutter
 * Date: 07-Jul-2009
 * Time: 14:47:15
 */
public abstract class Queries implements Constants {

    public static void addAction(String planetname, String rulername, BigDecimal action, String scanID, BigDecimal landingTick, String targetPlanet, String targetRuler) throws AddActionException {
        Connection conn = null;
        Statement st = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();

            conn = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_TABLE + "?" +
                    "user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD);


            st = conn.createStatement();
            String query = "INSERT INTO actions values(NULL, " + action.intValue() + ",'" + planetname + "','" + rulername + "','" + scanID + "'," + landingTick + ",'" + targetPlanet + "','" + targetRuler + "')";

            st.executeUpdate(query);
            st.close();
            conn.close();

        } catch (InstantiationException e) {

            throw new AddActionException(e.getMessage(), scanID, planetname, rulername, targetPlanet, targetRuler, landingTick);

        } catch (IllegalAccessException e) {
            throw new AddActionException(e.getMessage(), scanID, planetname, rulername, targetPlanet, targetRuler, landingTick);
        } catch (ClassNotFoundException e) {
            throw new AddActionException(e.getMessage(), scanID, planetname, rulername, targetPlanet, targetRuler, landingTick);
        } catch (SQLException e) {
            try {
                st.close();
                conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            if (e.getErrorCode() != 1062)  // we want to avoid end of processing cause of duplicate entry
                throw new AddActionException(e.getMessage(), scanID, planetname, rulername, targetPlanet, targetRuler, landingTick);
        }
    }

    public static BigDecimal getCurrentTick() throws CurrenttickNotFoundException {
        BigDecimal tick = new BigDecimal(0);
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_TABLE + "?" +
                    "user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD);


            st = conn.createStatement();
            rs = st.executeQuery("SELECT `value` from bot_system where `key`='" + CURRENT_TICK_SYSTEM_FIELD + "'");
            if (rs.next()) {
                tick = new BigDecimal(rs.getString(1));
            }
            rs.close();
            st.close();
            conn.close();

        } catch (InstantiationException e) {
            throw new CurrenttickNotFoundException("Could not find current tick: " + e.getMessage());
        } catch (IllegalAccessException e) {
            throw new CurrenttickNotFoundException("Could not find current tick: " + e.getMessage());
        } catch (ClassNotFoundException e) {
            throw new CurrenttickNotFoundException("Could not find current tick: " + e.getMessage());
        } catch (SQLException e) {
            try {
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

            throw new CurrenttickNotFoundException("Could not find current tick: " + e.getMessage());
        }

        return tick;
    }

    public static HashMap<String, Ship> getStats() {
        HashMap<String, Ship> ships = new HashMap<String, Ship>();
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_TABLE + "?" +
                    "user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD);


            st = conn.createStatement();
            rs = st.executeQuery("SELECT * from stats");
            while (rs.next()) {
                ships.put(rs.getString(2), new Ship(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getBigDecimal(8), rs.getBigDecimal(9), rs.getBigDecimal(10), rs.getBigDecimal(11), rs.getBigDecimal(12), rs.getBigDecimal(13), rs.getBigDecimal(14), rs.getBigDecimal(15)));
            }
            rs.close();
            st.close();
            conn.close();

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            try {
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

            e.printStackTrace();
        }

        return ships;
    }

    public static HashMap<String, String> getPlanetRulerFromCoords(String xTarget, String yTarget, String zTarget, BigDecimal tick) throws PlanetNotFoundException {

        HashMap<String, String> planetruler = new HashMap<String, String>();
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();

            conn = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_TABLE + "?" +
                    "user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD);


            BigDecimal currentTick = Queries.getCurrentTick();

            st = conn.createStatement();
            String query = null;
            if (currentTick.compareTo(tick) == 0) {
                query = "SELECT planetname,rulername from planets where x=" + xTarget + " AND y=" + yTarget + " AND z= " + zTarget;

            } else {
                query = "SELECT planetname,rulername from planets_history where tick=" + tick + " AND x=" + xTarget + " AND y=" + yTarget + " AND z=" + zTarget;
            }

            rs = st.executeQuery(query);

            if (rs.next()) {
                String planetname = rs.getString(1);
                String rulername = rs.getString(2);
                planetruler.put("planetname", planetname);
                planetruler.put("rulername", rulername);

            } else {
                rs.close();
                st.close();
                conn.close();
                throw new PlanetNotFoundException("", xTarget, yTarget, zTarget, tick);

            }

            rs.close();
            st.close();
            conn.close();

        } catch (InstantiationException e) {
            throw new PlanetNotFoundException(e.getMessage(), xTarget, yTarget, zTarget, tick);
        } catch (IllegalAccessException e) {
            throw new PlanetNotFoundException(e.getMessage(), xTarget, yTarget, zTarget, tick);
        } catch (ClassNotFoundException e) {
            throw new PlanetNotFoundException(e.getMessage(), xTarget, yTarget, zTarget, tick);
        } catch (SQLException e) {
            try {
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

            throw new PlanetNotFoundException(e.getMessage(), xTarget, yTarget, zTarget, tick);
        } catch (CurrenttickNotFoundException e) {
            throw new PlanetNotFoundException(e.getMessage(), xTarget, yTarget, zTarget, new BigDecimal(0));
        }
        return planetruler;
    }

    public static void addScan(String ID, BigDecimal scantype, String x, String y, String z, BigDecimal tick, BigDecimal userID) throws AddScanException {

        HashMap<String, String> planetruler = new HashMap<String, String>();
        Connection conn = null;
        Statement st = null;

        try {
            planetruler = getPlanetRulerFromCoords(x, y, z, tick);
        } catch (PlanetNotFoundException e) {
            throw new AddScanException(e.getError(), ID, x, y, z, scantype, tick, userID);

        }

        try {


            Class.forName("com.mysql.jdbc.Driver").newInstance();

            conn = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_TABLE + "?" +
                    "user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD);


            st = conn.createStatement();

            String query = "INSERT INTO scans VALUES ('" + ID + "'," + scantype + ",'" + planetruler.get("planetname") + "','" + planetruler.get("rulername") + "'," + tick + ")";

            st.executeUpdate(query);

            if (userID != null) {
                query = "INSERT INTO scan_user VALUES (NULL,'" + ID + "'," + userID + ")";
                st.executeUpdate(query);
            }
            st.close();
            conn.close();


        } catch (InstantiationException e) {
            throw new AddScanException(e.getMessage(), ID, x, y, z, scantype, tick, userID);
        } catch (IllegalAccessException e) {
            throw new AddScanException(e.getMessage(), ID, x, y, z, scantype, tick, userID);
        } catch (ClassNotFoundException e) {
            throw new AddScanException(e.getMessage(), ID, x, y, z, scantype, tick, userID);
        } catch (SQLException e) {

            try {
                st.close();
                conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

            throw new AddScanException(e.getMessage() + " " + e.getErrorCode() + " " + e.getSQLState(), ID, x, y, z, scantype, tick, userID);
        }
    }

    public static void addJGPLine(String scanID, BigDecimal fleettype, String x, String y, String z, Integer eta, Integer amount, String fleetname) throws AddJGPLineException {


        Connection conn = null;
        Statement st = null;

        try {


            Class.forName("com.mysql.jdbc.Driver").newInstance();

            conn = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_TABLE + "?" +
                    "user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD);


            st = conn.createStatement();
            String query = "INSERT INTO jumpgate VALUES (NULL,'" + scanID + "'," + fleettype + "," + x + "," + y + "," + z + ",'" + fleetname + "'," + amount + "," + eta + ")";
            st.executeUpdate(query);


            st.close();
            conn.close();


        } catch (InstantiationException e) {
            throw new AddJGPLineException(e.getMessage(), scanID, fleettype, x, y, z, eta, amount, fleetname);

        } catch (IllegalAccessException e) {
            throw new AddJGPLineException(e.getMessage(), scanID, fleettype, x, y, z, eta, amount, fleetname);
        } catch (ClassNotFoundException e) {
            throw new AddJGPLineException(e.getMessage(), scanID, fleettype, x, y, z, eta, amount, fleetname);
        } catch (SQLException e) {

            try {
                st.close();
                conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

            throw new AddJGPLineException(e.getMessage(), scanID, fleettype, x, y, z, eta, amount, fleetname);
        }
    }

    public static void addPlanetDetails(String scanID, String x, String y, String z, BigDecimal tick, BigDecimal agents, BigDecimal securityGuards, BigDecimal mroids, BigDecimal croids, BigDecimal eroids, BigDecimal mresources, BigDecimal cresources, BigDecimal eresources, BigDecimal prodres, BigDecimal lightprod, BigDecimal medprod, BigDecimal heavyprod, String light, String medium, String heavy) throws AddPlanetException {

        Connection conn = null;
        Statement st = null;

        try {


            Class.forName("com.mysql.jdbc.Driver").newInstance();

            conn = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_TABLE + "?" +
                    "user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD);


            st = conn.createStatement();
            String query = "INSERT INTO planet VALUES (NULL,'" + scanID + "'," + securityGuards + "," + agents + "," + mroids + "," + croids + "," + eroids + "," + mresources + "," + cresources + "," + eresources + "," + prodres + ",'" + light + "','" + medium + "','" + heavy + "'," + lightprod + "," + medprod + "," + heavyprod + ")";

            st.executeUpdate(query);


            st.close();
            conn.close();


        } catch (InstantiationException e) {
            throw new AddPlanetException(e.getMessage(), x, y, z, tick, scanID);

        } catch (IllegalAccessException e) {
            throw new AddPlanetException(e.getMessage(), x, y, z, tick, scanID);
        } catch (ClassNotFoundException e) {
            throw new AddPlanetException(e.getMessage(), x, y, z, tick, scanID);
        } catch (SQLException e) {

            try {
                st.close();
                conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

            throw new AddPlanetException(e.getSQLState(), x, y, z, tick, scanID);
        }
    }

    public static void addDevelopmentAnalyse(String scanID, String x, String y, String z, BigDecimal tick, BigDecimal lightFactory, BigDecimal mediumFactory, BigDecimal heavyFactory, BigDecimal amplifier, BigDecimal distorter, BigDecimal metalRefinery, BigDecimal crystalRefinery, BigDecimal eoniumRefinery, BigDecimal researchLab, BigDecimal financeCentre, BigDecimal securityCentre, BigDecimal spaceTravel, BigDecimal infraStructure, BigDecimal hulls, BigDecimal waves, BigDecimal coreExtraction, BigDecimal covertOps, BigDecimal asteroidMining) throws AddDevelopmentException {
        Connection conn = null;
        Statement st = null;

        try {


            Class.forName("com.mysql.jdbc.Driver").newInstance();

            conn = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_TABLE + "?" +
                    "user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD);


            st = conn.createStatement();
            String query = "INSERT INTO development VALUES (NULL,'" + scanID + "'," + lightFactory + "," + mediumFactory + "," + heavyFactory + "," + amplifier + "," + distorter + "," + metalRefinery + "," + crystalRefinery + "," + eoniumRefinery + "," + researchLab + "," + financeCentre + "," + securityCentre + "," + spaceTravel + "," + infraStructure + "," + hulls + "," + waves + "," + coreExtraction + "," + covertOps + "," + asteroidMining + ")";


            st.executeUpdate(query);


            st.close();
            conn.close();


        } catch (InstantiationException e) {
            throw new AddDevelopmentException(e.getMessage(), scanID, x, y, z, tick);

        } catch (IllegalAccessException e) {
            throw new AddDevelopmentException(e.getMessage(), scanID, x, y, z, tick);
        } catch (ClassNotFoundException e) {
            throw new AddDevelopmentException(e.getMessage(), scanID, x, y, z, tick);
        } catch (SQLException e) {

            try {
                st.close();
                conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

            throw new AddDevelopmentException(e.getSQLState(), scanID, x, y, z, tick);
        }

    }

    public static void addShipStat(String race, String name, String klasse, String t1, String t2, String t3, String type, String init, String guns, String armour, String damage, String empres, String metal, String crystal, String eonium) {
        Connection conn = null;
        Statement st = null;
        String query = "";
        try {


            Class.forName("com.mysql.jdbc.Driver").newInstance();

            conn = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_TABLE + "?" +
                    "user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD);


            st = conn.createStatement();
            if (damage.equals("-")) {
                damage = "0";
            }
            query = "INSERT INTO stats VALUES (NULL,'" + race + "','" + name + "','" + klasse + "','" + t1 + "','" + t2 + "','" + t3 + "','" + type + "'," + init + "," + guns + "," + armour + "," + damage + "," + empres + "," + metal + "," + crystal + "," + eonium + ")";


            st.executeUpdate(query);


            st.close();
            conn.close();


        } catch (InstantiationException e) {
            e.printStackTrace();

        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println(query);
            try {
                st.close();
                conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }


        }

    }

    public static void addShipToScan(String ship, BigDecimal amount, String scanID) {
        Connection conn = null;
        Statement st = null;
        String query = "";
        try {


            Class.forName("com.mysql.jdbc.Driver").newInstance();

            conn = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_TABLE + "?" +
                    "user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD);


            st = conn.createStatement();

            query = "INSERT INTO unit VALUES (NULL,'" + scanID + "','" + ship + "'," + amount + ")";

            st.executeUpdate(query);

            st.close();
            conn.close();


        } catch (InstantiationException e) {
            e.printStackTrace();

        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println(query);
            try {
                st.close();
                conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    public static ArrayList<BigDecimal> getChantypes(String channel) {
        ArrayList<BigDecimal> chantypes = new ArrayList<BigDecimal>();
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_TABLE + "?" +
                    "user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD);


            st = conn.createStatement();
            rs = st.executeQuery("SELECT distinct channel_type from channels where name='" + channel + "'");
            while (rs.next()) {
            chantypes.add(rs.getBigDecimal(1));
            }
            rs.close();
            st.close();
            conn.close();

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            try {
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

            e.printStackTrace();
        }
        return chantypes;
    }
}
